<?php
/*
  2013/04/19 Search_Model_Yachinsoubasikucyoson へ統合
  現在使用していない
*/

class Search_Model_Yachinsoubasikucyosonsyosai extends Search_Model_Yachinsouba{

	/**
	* 家賃相場（市区町村）データ取得
	*/
	public function getYachinsoubaSikucyosonSyosaiData($params)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaSikucyosonSyosaiSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* グラフデータ取得
	*/
	public function getYachinsoubaSikucyosonGraphData($params)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaSikucyosonGraphSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

   /**
	* 家賃相場（市区町村）を表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertYachinsoubaSikucyosonSyosaiData($result,$result_graph,&$data)
	{
		$display = $this->initArray();

		//グラフ縦幅の等分数
		$setcount = 5;

		foreach ($result as $record){
			$this->getMadoriAvg($record,$display);
		}
		$data['yachinsoubasikucyosonsyosai'] = $display;
		$data['graph'] = $result_graph;

		$count = 0;
		$price=Array();
		foreach ($result_graph as $record){
			$price[$count] = $record['PRICE'];
			$count++;
		}
		//値段の最大値
		if (!empty($price)) {
			$max_price = max($price);
		} else {
			$max_price = 100;
		}
		//縦軸の最大値
		$gridmax = 0;

		//最大値が1万円未満
		if($max_price < 10000){
			$gridmax = 1;
		}else{

			for($i = $setcount;$i<=1000;$i=$i + $setcount){
				if($max_price < $i * 10000){
					$gridmax = $i;
					break;
				}
			}
		}
		$interval = $gridmax / $setcount;

		//縦軸ラベル
		$label = Array();
		for($i = 0;$i <= $setcount;$i++){
			$label[$i] = $i * $interval;
		}
		$data['label_y'] =implode("|",$label);
		$data['graph_range_y'] = 100 / $setcount;

		$count = 0;
		$price_prot=Array();
		foreach ($result_graph as $record){
			$price_prot[$count] = $record['PRICE'] / ($gridmax * 100);
			$count++;
		}
		$data['graph_y'] = implode(",",$price_prot);
	}

	public function getMadoriAvg($result,&$display)
	{
		$sum = $this->initArray();
		$count = $this->initArray();

		$madori = $result['MADORI'];
		$yachin = $result['YACHIN_AVG'];
		$tsuboyachin = $result['TSUBOYACHIN_AVG'];
		$shikikiin = $result['SHIKIKIN_AVG'];
		$reikin = $result['REIKIN_AVG'];


		//1R
		if($madori == '1R'){
			$display[0][0] = sprintf("%.2f", $yachin/ 10000);
			$display[1][0] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][0] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][0] = sprintf("%.2f", $reikin / 10000);

		//1K/1DK
		}else if($madori == '1K'){
			$display[0][1] = sprintf("%.2f", $yachin/ 10000);
			$display[1][1] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][1] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][1] = sprintf("%.2f", $reikin / 10000);
		//1LDK
		}else if($madori == '1LDK' ){
			$display[0][2] = sprintf("%.2f", $yachin/ 10000);
			$display[1][2] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][2] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][2] = sprintf("%.2f", $reikin / 10000);
		//2K/2DK
		}else if($madori == '2K'){
			$display[0][3] = sprintf("%.2f", $yachin/ 10000);
			$display[1][3] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][3] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][3] = sprintf("%.2f", $reikin / 10000);
		//2LDK
		}else if($madori == '2LDK' ){
			$display[0][4] = sprintf("%.2f", $yachin/ 10000);
			$display[1][4] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][4] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][4] = sprintf("%.2f", $reikin / 10000);
		//3K/3DK
		}else if($madori == '3K'){
			$display[0][5] = sprintf("%.2f", $yachin/ 10000);
			$display[1][5] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][5] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][5] = sprintf("%.2f", $reikin / 10000);
		//3K/3DK
		}else if($madori == '3LDK'){
			$display[0][6] = sprintf("%.2f", $yachin/ 10000);
			$display[1][6] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][6] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][6] = sprintf("%.2f", $reikin / 10000);
		//4k～
		}else if($madori == '4K'){
			$display[0][7] = sprintf("%.2f", $yachin/ 10000);
			$display[1][7] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][7] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][7] = sprintf("%.2f", $reikin / 10000);
		}

	}

     /**
      * 賃料（万円台）×間取り件数２次元配列の初期化
     * @access	public
     * @return display
     */
	public function initArray(){

			$display= array();

			$test1 = array(0,1,2,3);
			$test2 = array(0,1,2,3,4,5,6,7);

			foreach($test1 as $val1){
				foreach($test2 as $val2){
					$display[$val1][$val2] = 0;
				}
			}
			return $display;
	}

    /**
     * 家賃相場（市区町村）データ検索用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 家賃相場（沿線）データ検索用SQL
     */
	public function generateYachinsoubaSikucyosonSyosaiSql($params, &$db_params)
	{
		// 表示日を取得
		$ymd = $this->getYachinsoubaDate();
		if (!empty($ymd))
			$db_params['ymd'] = $ymd['YMD'];
		else
			$db_params['ymd'] = date('Ymd')-1;
		
		$db_params['CityCd'] = $params['CityCd'][0];
		
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M01.M01_TODOUFUKEN   as PAR_NAME, ";
		$sql .= "    M01.M01_SHIKUCHOUSON as KEY_NAME, ";
		$sql .= "    ROUND(AVG(A02_YACHIN_AVG)) AS YACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_TSUBOYACHIN_AVG)) AS TSUBOYACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_SHIKIKIN_AVG)) AS SHIKIKIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_REIKIN_AVG)) AS  REIKIN_AVG, ";
		$sql .= "    MADORI ";
		$sql .= "FROM (";
		$sql .= "  SELECT ";
		$sql .= "      A02_KEY_CD, ";
		$sql .= "      A02_YACHIN_AVG, ";
		$sql .= "      A02_TSUBOYACHIN_AVG, ";
		$sql .= "      A02_SHIKIKIN_AVG, ";
		$sql .= "      A02_REIKIN_AVG, ";
		$sql .= "      CASE ";
		$sql .= "        WHEN A02_MADORI_CD in ('10') THEN '1R'";
		$sql .= "        WHEN A02_MADORI_CD in ('11','12','13') THEN '1K'";
		$sql .= "        WHEN A02_MADORI_CD in ('14','15') THEN '1LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('20','21','22','23') THEN '2K'";
		$sql .= "        WHEN A02_MADORI_CD in ('24','25') THEN '2LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('30','31','32','33') THEN '3K'";
		$sql .= "        WHEN A02_MADORI_CD in ('34','35') THEN '3LDK'";
		$sql .= "        WHEN A02_MADORI_CD >= '40' and A02_MADORI_CD < '80' THEN '4K'";
		$sql .= "        ELSE '99'";
		$sql .= "      END AS MADORI ";
		$sql .= "  FROM ";
		$sql .= "      A02ESTATE_MKT_PRICE_DAY ";
		$sql .= "  WHERE ";
		$sql .= "      A02_DATA_KUBUN = 11 AND ";		// 市区町村
		//本運用ではコメントを外す
		$sql .= "    A02_TARGET_YMD  = :ymd AND ";
		if($params['type'] == 2){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('2') AND";
		}else if($params['type'] == 3){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','3','4','10') AND";
		}else if($params['type'] == 4){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('5','6','7','8','9') AND";
		}else {
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','2','3','4','5','6','7','8','9','10') AND";
		}
		$sql .= "      A02_KEY_CD = :CityCd ";
		$sql .= "  ) A02";
		$sql .= ",( SELECT DISTINCT";
		$sql .= "      M01_TODOUFUKEN_CD ";
		$sql .= "    , M01_TODOUFUKEN ";
		$sql .= "    , M01_SHIKUCHOUSON_CD ";
		$sql .= "    , M01_SHIKUCHOUSON ";
		$sql .= "   FROM M01JYUUSHO ";
		$sql .= "   WHERE M01_TODOUFUKEN_CD || M01_SHIKUCHOUSON_CD = :CityCd ";
		$sql .= " ) M01 ";
		$sql .= "WHERE MADORI <> '99'";
		$sql .= "  AND A02.A02_KEY_CD = M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD ";
		$sql .= "GROUP BY ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M01.M01_TODOUFUKEN, ";
		$sql .= "    M01.M01_SHIKUCHOUSON, ";
		$sql .= "    MADORI ";
		
		return $sql;
	}


    /**
     * 家賃相場（市区町村）データグラフ用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 家賃相場（沿線）データグラフ用SQL
     */
    public function generateYachinsoubaSikucyosonGraphSql($params,&$db_params)
    {
    	$db_params['CityCd'] = $params['CityCd'][0];

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    TO_CHAR(TO_DATE(a.A02_TARGET_YM,'YYYYMM'),'YYYY/MM') AS MONTH, ";
		//賃料
    	if($params['graphcode'] == 1){
			$sql .= "   AVG(a.A02_YACHIN_AVG) AS PRICE ";
		//坪賃料
    	}else if($params['graphcode'] == 2){
    		$sql .= "   AVG(a.A02_TSUBOYACHIN_AVG)  AS PRICE ";
    	//敷金
		}else if($params['graphcode'] == 3){
			$sql .= "   AVG(a.A02_SHIKIKIN_AVG)  AS PRICE ";
		//礼金
		}else{
			$sql .= "   AVG(a.A02_REIKIN_AVG)  AS PRICE ";
		}
    	$sql .= "FROM ";
		$sql .= "    WEBLOG1_USER.A02ESTATE_MKT_PRICE_MONTH a ";
		$sql .= "WHERE ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') >= ADD_MONTHS(SYSDATE,-13) AND ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') <= ADD_MONTHS(SYSDATE,-1) AND ";
		$sql .= "    a.A02_KEY_CD = :CityCd AND ";
		//1R/1K/1DK
		if($params['madori'] == 1){
			$sql .= "    a.A02_MADORI_CD IN ('10','11','12','13') ";
		//1LDK/2K/2DK
		}else if($params['madori'] == 2){
			$sql .= "    a.A02_MADORI_CD IN ('14','15','20','21','22','23') ";
		//2LDK/3K/3DK
		}else if($params['madori'] == 3){
			$sql .= "    a.A02_MADORI_CD IN ('24','25','30','31','32','33') ";
		//3LDK/4K/4DK
		}else{
			$sql .= "    a.A02_MADORI_CD IN ('34','35','40','41','42','43') ";
		}
		$sql .= " AND ";
		$sql .= "    a.A02_DATA_KUBUN = '11' ";
		$sql .= "GROUP BY  ";
		$sql .= "    a.A02_TARGET_YM ";
		$sql .= "ORDER BY  ";
		$sql .= "    a.A02_TARGET_YM ";

	   return $sql;
    }
}

?>
